package com.brother.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.PrintStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JOptionPane;

import com.brother.database.DataBase;

public class BackupUtils {

	private static String currentDate = "";
	private static String datePath = "";
	public static void runBackup(){
		try{
//			 alarm
//			 alarmtype
//			 carmodel
//			 cooraddress
//			 emailsetting
//			 realtimedata
//			 tracker
//			 user
//			conn = DriverManager.getConnection("jdbc:mysql://"+this.host.getText()+":"+port.getText()
//					+"/gpsMap?characterEncoding=utf-8", account.getText(), password.getText());
//			Statement stmt = conn.createStatement();
			
			String path = CommonProperties.getBackupPath();
			File file = new File(path);
			if(!file.exists()){
				file.mkdirs();
			}
			SimpleDateFormat sf = new SimpleDateFormat("yyyyMMdd");
			String now = sf.format(new Date());
			if(now.equals(currentDate)){ // 已经备份了
				return;
			}
			datePath = path + "/" + now;
			file = new File(datePath);
			if(!file.exists()){
				file.mkdirs();
			}else{
				return; // 已经备份了
			}
			createTable("alarm");
			createTable("alarmType");
			
			createTable("carmodel");
			createTable("emailsetting");
			
			createTable("tracker");
			
			createTable("user");
			currentDate = now;
		}catch(Exception e){
			JOptionPane.showMessageDialog(null, "There is an error when backing up data. Please take" +
					" a screenshot and contact technical support. Error: "+e.toString());
			try {
				PrintStream errorPrint = new PrintStream(datePath+"/run_error.txt");
				e.printStackTrace(errorPrint);
				errorPrint.close();
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
			}
			
		}finally{
			DataBase.closeConnection();
		}
	}	
	
	
	private static void createTable(String tableName) throws SQLException, IOException {
		
		
		File file = new File(datePath+"/"+tableName+".sql");
		FileOutputStream out2 = new FileOutputStream(file);
		OutputStreamWriter out = new OutputStreamWriter(out2, "utf-8");
		
		String sql = "SHOW create TABLE "+tableName; 
//		ResultSet rs = stmt.executeQuery(sql);
//		rs.next();
//		out.write(rs.getString(2)+"\r\n");
		
		sql = "select * from "+tableName;
		ResultSet rs = DataBase.query(sql);
		out.write(("LOCK TABLES `"+tableName+"` WRITE;\r\n"));
		String fields = "";
		ResultSetMetaData meta = rs.getMetaData();
		for(int i=1; i <= meta.getColumnCount(); i++){
			if(i > 1)
				fields = fields + ",`"+meta.getColumnName(i)+"`";
			else if(i == 1){
				fields = "`"+meta.getColumnName(i)+"`";
			}
		}
		int count = 1;
		
		while(rs.next()){
			if(count == 1){
				out.write(("INSERT INTO `"+tableName+"`("+fields+") VALUES "));
			}
			if(rs.isLast() || count == 15){ // 每15行做一个分号
				String value = getValue(meta, rs);
				if(count > 1){ // 第二行以上的数据
					value = ","+ value;
				}
				value = value + ";\r\n";
				out.write(value);
				count = 0;
			}else{
				String value = getValue(meta, rs);
				if(count > 1){ // 第二行以上的数据
					value = ","+ value;
				}
				out.write(value);
			}
			count++;
		}
		out.write("UNLOCK TABLES;\r\n");
		out.close();
		rs.close();
	}

	private static String getValue(ResultSetMetaData meta, ResultSet rs) throws SQLException {
		String result = "(";
		for(int i=1; i <= meta.getColumnCount(); i++){
			int type = meta.getColumnType(i);
			if(i == 1){
				result = result + getColumn(i, rs, type);
			}else if(i > 1){
				result = result +","+ getColumn(i, rs, type);
			}
		}
		result = result+")";
		return result;
	}

	private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	
	private static String getColumn(int columnIndex, ResultSet rs, int type) throws SQLException {
		if(type == Types.INTEGER ||
				type == Types.FLOAT
				|| type == Types.SMALLINT
				|| type == Types.TINYINT
				|| type == Types.DECIMAL
				|| type == Types.REAL
				|| type == Types.BIT
				|| type == Types.DOUBLE){
			String s = rs.getString(columnIndex);
			if(s == null){
				return "null";
			}
			return s;
		}else if(type == Types.VARCHAR
				|| type == Types.CHAR
				|| type == Types.LONGVARCHAR
				|| type == Types.BLOB){
			String s = rs.getString(columnIndex);
			if(s == null){
				return "null";
			}
			return "'"+s.replaceAll("'", "\\\\'")+"'";
		}else if(type == Types.DATE
				|| type == Types.TIME
				|| type == Types.TIMESTAMP){
			Date s = rs.getTimestamp(columnIndex);
			if(s == null){
				return "null";
			}
			return "'"+df.format(s)+"'";
		}
		System.out.println("type="+type+",column="+rs.getMetaData().getColumnName(columnIndex));
		Object o = rs.getObject(columnIndex);
		if(o == null)
			return "null";
		return o.toString();
	}
	
}
